﻿@using Seal.Model;
@{
    ReportTask task = Model;

    task.TemplateDescription = "Load an Excel file and save a given tab in a database table.";

    //Parameters for this task
    task.Parameters.Add(new Parameter() { Name = "excel_path", Value = "", DisplayName = "Excel file path", Description = "The Excel file path (*.xlsx)." + ReportTask.TranslatedParameterDescriptionFull, Enums = new string[] { @"%SEALREPOSITORY%/Databases/databank.worldbank.org_health_population.xlsx", ReportTask.ParentExecResultKeyword, ReportTask.ExecInputKeyword }, UseOnlyEnumValues = false });
    task.Parameters.Add(new Parameter() { Name = "excel_tab", Value = "", DisplayName = "Tab name", Description = "The tab name of the Excel Sheet used to load the table. If empty, all the tabs are loaded. The tab name can contains wild char * to process only tabs having a given pattern (e.g. 'table_*' or '*tab' or '*mid*'). " + ReportTask.TranslatedParameterDescription });
    task.Parameters.Add(new Parameter() { Name = "table_name", Value = "", DisplayName = "Destination table name or prefix", Description = "The name of the destination table created in the database or the table prefix if several tabs are loaded (if 'Tab name' is empty or contains wild char *). If empty, the name of the tab is used." + ReportTask.TranslatedParameterDescription });
    task.Parameters.Add(new Parameter() { Name = "excel_start_row", NumericValue = 1, DisplayName = "Start row", Description = "The start row of the tab to define the table." });
    task.Parameters.Add(new Parameter() { Name = "excel_start_col", NumericValue = 1, DisplayName = "Start column", Description = "The start column of the tab to define the table." });
    task.Parameters.Add(new Parameter() { Name = "excel_end_row", NumericValue = 0, DisplayName = "End row", Description = "The end row to define the table. If 0, rows are taken until an empty row is found." });
    task.Parameters.Add(new Parameter() { Name = "excel_end_col", NumericValue = 0, DisplayName = "End column", Description = "The end column to define the table. If 0, columns are taken until an empty column is found." });
    task.Parameters.Add(new Parameter() { Name = "excel_header", BoolValue = true, DisplayName = "Has header", Description = "If true, the first line is used to get the column names otherwise column names are automatic." });
    task.Parameters.Add(new Parameter() { Name = "force_load", BoolValue = false, DisplayName = "Force load", Description = "If true, the load is always performed, even if the Excel file was not modified. In this case the 'Loaded folder' parameter is not used. " });
    task.Parameters.Add(new Parameter() { Name = "excel_folder", Value = "", DisplayName = "Loaded folder", Description = "If 'Force load' is false, the folder used to store the loaded Excel files. If empty or single name, a sub-folder named is created having the specified name ('Loaded' by default)." + ReportTask.TranslatedParameterDescriptionFull, Enums = new string[] { @"%SEALREPOSITORY%/TestFiles/Loaded", ReportTask.ParentExecResultKeyword, ReportTask.ExecInputKeyword }, UseOnlyEnumValues = false });

    task.Script = @"@using System.IO
@using System.Data
@{
    ReportTask task = Model;
    var helper = new TaskHelper(task);
    
    helper.LoadTableFromExcel(
        task.GetValueTranslated(""excel_folder""), 
        task.GetValueTranslated(""excel_path""), 
        task.GetValueTranslated(""excel_tab""), 
        task.GetValueTranslated(""table_name""), 
        task.ExecuteForEachConnection, 
        task.GetNumericValue(""excel_start_row""), 
        task.GetNumericValue(""excel_start_col""), 
        task.GetNumericValue(""excel_end_col""), 
        task.GetNumericValue(""excel_end_row""), 
        task.GetBoolValue(""excel_header"", true),
        task.GetBoolValue(""force_load"", false)
    );
}
";

    task.BodyScript = ReportTask.BodyScriptTemplate;
}
